import java.sql.*;
import java.util.*;
public abstract class Db
{
	Connection conn;
	
    public Db()
    {
        connect();
    }
    
    public abstract void connect();
    
    public ArrayList<Person> getAllPersons()
    {
    	ArrayList<Person> result = new ArrayList<Person>();
    	try
    	{
	    	Statement stmt = conn.createStatement();
	    	ResultSet rs = stmt.executeQuery("select * from Person");
	    	while (rs.next())
	    	{
	    		result.add(new Person(rs.getString("firstname"), rs.getString("lastname"), rs.getInt("birthyear"), rs.getInt("childcount")));
	    	}
    	}
    	catch (Exception e)
    	{
    		return new ArrayList<Person>();
    	}
    	return result;
    }

    public ArrayList<Person> getChildlessPersons()
    {
    	ArrayList<Person> result = new ArrayList<Person>();
    	try
    	{
	    	Statement stmt = conn.createStatement();
	    	ResultSet rs = stmt.executeQuery("select * from Person where childcount = 0");
	    	while (rs.next())
	    	{
	    		result.add(new Person(rs.getString("firstname"), rs.getString("lastname"), rs.getInt("birthyear"), rs.getInt("childcount")));
	    	}
    	}
    	catch (Exception e)
    	{
    		return new ArrayList<Person>();
    	}
    	return result;

    }
    
    public ArrayList<Person> searchLastName(String lastname)
    {
    	ArrayList<Person> result = new ArrayList<Person>();
    	try
    	{
	    	Statement stmt = conn.createStatement();
	    	ResultSet rs = stmt.executeQuery("select * from Person where LOWER(lastname) like LOWER('%" + lastname + "%')");
	    	while (rs.next())
	    	{
	    		result.add(new Person(rs.getString("firstname"), rs.getString("lastname"), rs.getInt("birthyear"), rs.getInt("childcount")));
	    	}
    	}
    	catch (Exception e)
    	{
    		return new ArrayList<Person>();
    	}
    	return result;

    }
}
